In this example a new Custom Column will be created that switches its values based on the user using the column. In effect, this technique creates a personalized column that can be used to drive dashboards, reports and publications in different ways based on who is using the content item at the time.
Step-by-step example
Step 1
Start by deciding how you will determine who the user is. This includes:
- their username
- their first name, last name or both
- their email address
- their tenant
- their custom data flags (usually set when a user is logged into Pyramid programmatically)
- one or more of their roles
Each of these elements is inherent in a user's identity in the platform and can easily be derived from the PQL function library, identity category.
The more complex method, is to use a column from a user lookup table - which is similar to a security or distribution table described here. If you choose this method, the lookup table is typically UNJOINED (or has NO SQL relationship) with the to all resulting columns. Great care should be taken to ensure that the user selection mechanism in this technique does not create a multiplying cross-join effect with the results
Step 2
Launch Formulate and then open the Custom Column editor, picking the data model against which this column will be acting. For more information, see Custom Column Examples.
Start by writing a formula that checks the one of the user's properties, as described in step 1, through a simple IF statement. As shown below, the criteria check for the IF statement is using the built-in PQL UserName function, which was dragged-and-dropped from the menu o the left (red arrow).
The criteria statement is checking to see if the username of the current user is equal to "MTEST1" in this basic example (green line above). If it is, the TRUE part of the IF resolves to the country attribute, while the FALSE part resolves to the state attribute.
With this simple formula, the new column will adjust based on who is using the column.
Step 3
To handle multiple users, one could create numerous, NESTED IF statements. However this would be cumbersome to write and manage. An alternative is to use the CASE and SWITCH functions, found in the Logical category, which provides a simpler method for handling multiple use cases. This is shown below using the scripting interface, rather than the more graphical UI for custom columns.
